package com.airline.dao;

import com.airline.entity.Flight;
import com.airline.util.DBUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class FlightDAO {

    /**
     * 查询所有航班
     */
    public List<Flight> findAll() {
        List<Flight> flights = new ArrayList<>();
        String sql = "SELECT * FROM flight";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Flight flight = buildFlightFromResultSet(rs);
                flights.add(flight);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flights;
    }

    /**
     * 按航班号查询单个航班
     */
    public Flight findByFlightNo(String flightNo) {
        Flight flight = null;
        String sql = "SELECT * FROM flight WHERE flight_no = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, flightNo);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                flight = buildFlightFromResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flight;
    }

    /**
     * 新增：按条件筛选航班（出发地、目的地、价格区间，支持可选条件）
     * 空参数表示不限制该条件
     */
    public List<Flight> findByCondition(String departure, String destination,
                                        BigDecimal minPrice, BigDecimal maxPrice) {
        List<Flight> flights = new ArrayList<>();
        // 基础 SQL，1=1 方便后续拼接 AND 条件
        StringBuilder sql = new StringBuilder("SELECT * FROM flight WHERE 1=1");
        // 存储参数值（用于 PreparedStatement 绑定，避免 SQL 注入）
        List<Object> params = new ArrayList<>();

        // 拼接出发地条件（非空才添加）
        if (departure != null && !departure.trim().isEmpty()) {
            sql.append(" AND departure = ?");
            params.add(departure.trim());
        }

        // 拼接目的地条件（非空才添加）
        if (destination != null && !destination.trim().isEmpty()) {
            sql.append(" AND destination = ?");
            params.add(destination.trim());
        }

        // 拼接最低价格条件（非空才添加）
        if (minPrice != null) {
            sql.append(" AND price >= ?");
            params.add(minPrice);
        }

        // 拼接最高价格条件（非空才添加）
        if (maxPrice != null) {
            sql.append(" AND price <= ?");
            params.add(maxPrice);
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            // 绑定参数（按拼接顺序赋值）
            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i);
                if (param instanceof String) {
                    pstmt.setString(i + 1, (String) param);
                } else if (param instanceof BigDecimal) {
                    pstmt.setBigDecimal(i + 1, (BigDecimal) param);
                }
            }

            // 执行查询并封装结果
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Flight flight = buildFlightFromResultSet(rs);
                flights.add(flight);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flights;
    }

    /**
     * 工具方法：从 ResultSet 封装 Flight 对象（复用代码，避免冗余）
     */
    private Flight buildFlightFromResultSet(ResultSet rs) throws SQLException {
        Flight flight = new Flight();
        flight.setFlightNo(rs.getString("flight_no"));
        flight.setDeparture(rs.getString("departure"));
        flight.setDestination(rs.getString("destination"));
        flight.setDepartureTime(rs.getObject("departure_time", LocalDateTime.class));
        flight.setArrivalTime(rs.getObject("arrival_time", LocalDateTime.class));
        flight.setPrice(rs.getBigDecimal("price"));
        return flight;
    }

    /**
     * 新增：判断航班是否存在（用于订票时校验）
     */
    public boolean existsFlight(String flightNo) {
        String sql = "SELECT COUNT(*) FROM flight WHERE flight_no = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, flightNo);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0; // 存在返回 true，否则 false
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
